﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Web.SessionState;

namespace elctronixMVC.Models
{
    public class siparisler
    {





        public int siparisID { get; set; }
        public string sepet { get; set; }
        public string sepeturunID { get; set; }
        public int productID { get; set; }
        public string productname { get; set; }
        public string model { get; set; }
        public decimal price { get; set; }
        public string brand { get; set; }
        public int categoryID { get; set; }
        public decimal sepettutar { get; set; }
        public int sepetmiktar { get; set; }
        public string userID { get; set; }
        public string orderdate { get; set; }  //siparis tarihi
        public string requireddate { get; set; }  //teslimat tarih
        public string shippeddate { get; set; } //teslim tarihi
        public int shipvia { get; set; }  // hangi kargo
        public decimal frieght { get; set; } // kargo ücreti
        public string shipaddres { get; set; }  // teslimat adresi , siparis onaylandıgında 
        public string invoiceaddres { get; set; }  // fatura adresi , siparis onaylandıgında 
        public string odemeyontemi { get; set; }
        public int status { get; set; }
        public string siparisveren { get; set; }
        public string telefon { get; set; }
        public string email { get; set; }
        public string faturaadresi { get; set; }
        public string teslimatadresi { get; set; }
        public decimal discount { get; set; }

        public int kalem { get; set; }

        electronicDataDataContext db = new electronicDataDataContext();


        public HttpCookie sepeteekle()
        {
            string sepetisim = "sepet" + userID;
            HttpCookie kuki = new HttpCookie(sepetisim, sepet);
            kuki.Expires = DateTime.Now.AddMinutes(60 * 24 * 7);
            return kuki;
            // 3. basamak = oluşturulan cookie cliente eklenir 
            // Response.Cookies.Add(kuki);
        }


        public List<siparisler> sepetiyazdir()
        {
            sepettutar = 0;
            kalem = 0;

            List<siparisler> sip = new List<siparisler>();
            string[] sepetdizi = sepet.Split('|');
            sepetmiktar = sepetdizi.Length - 1;
            SqlConnection sqlcon = connection.baglanti;
            for (int i = 1; i < sepetdizi.Length; i++)
            {
                sqlcon.Open();
                SqlCommand sqlcmd = new SqlCommand("select * from products where productID=@pid", sqlcon);
                sqlcmd.Parameters.AddWithValue("@pid", sepetdizi[i]);
                SqlDataReader sdr = sqlcmd.ExecuteReader();
                while (sdr.Read())
                {
                    siparisler s = new siparisler();
                    s.brand = sdr["brand"].ToString();
                    s.model = sdr["model"].ToString();
                    s.productname = sdr["productname"].ToString();
                    s.productID = Convert.ToInt32(sdr["productID"]);
                    s.price = Convert.ToDecimal(sdr["price"]);
                    sepettutar += s.price;
                    kalem++;
                    s.categoryID = Convert.ToInt32(sdr["categoryID"]);
                    sip.Add(s);
                }
                sqlcon.Close();
            }
            return sip;
        }


        public void sepetduzenle(string scid)
        {
            string[] sepetdizi = sepet.Split('|');
            string yenisepet = "";
            int miktar = 0;
            for (int i = 0; i < sepetdizi.Length; i++)
            {
                if (sepetdizi[i] != scid)
                {
                    yenisepet += "|" + sepetdizi[i];
                }
                else if (miktar >= 1)
                {
                    yenisepet += "|" + sepetdizi[i];
                }
                else
                {
                    miktar = 1;
                }
            }
            sepet = yenisepet;
        }


        public void siparisolustur() // siparis kaydı yapıyoruz.sonra o siparis numarasını databaseden cekiyoruz,o siparisin kalemlerini orderdetails tablosuna giriyoruz
        {
            try
            {
                SqlConnection sqlcon = connection.baglanti;
                SqlCommand sqlcmd = new SqlCommand("insert into orders values(@userID,@orderdate,@reqdate,@shippeddate,@shipvia,@fright,@invoice,@shipaddress,@odemeyontemi,@status)", sqlcon);
                sqlcmd.Parameters.AddWithValue("@userID", userID);
                sqlcmd.Parameters.AddWithValue("@orderdate", orderdate);
                sqlcmd.Parameters.AddWithValue("@reqdate", requireddate);
                sqlcmd.Parameters.AddWithValue("@shippeddate", shippeddate);
                sqlcmd.Parameters.AddWithValue("@shipvia", shipvia);
                sqlcmd.Parameters.AddWithValue("@fright", frieght);
                sqlcmd.Parameters.AddWithValue("@invoice", invoiceaddres);
                sqlcmd.Parameters.AddWithValue("@shipaddress", shipaddres);
                sqlcmd.Parameters.AddWithValue("@odemeyontemi", "paypal");
                sqlcmd.Parameters.AddWithValue("@status", status);
                sqlcon.Open();
                sqlcmd.ExecuteNonQuery();
                sqlcon.Close();

                SqlCommand sqlcmd2 = new SqlCommand("select top(1)orderID from orders where userID=@userID order by OrderDate desc", sqlcon);
                sqlcmd2.Parameters.AddWithValue("@userID", userID);
                sqlcon.Open();
                siparisID = Convert.ToInt32(sqlcmd2.ExecuteScalar());  // tek kayıt oldugu icin
                sqlcon.Close();

                List<siparisler> lst = sepetiyazdir();
                sqlcon.Open();
                foreach (var item in lst)
                {
                    SqlCommand sqlcmd3 = new SqlCommand("insert into orderdetails values (@orderID,@productID,@price,@quantity,@discount)", sqlcon);
                    sqlcmd3.Parameters.AddWithValue("@orderID", siparisID);
                    sqlcmd3.Parameters.AddWithValue("@productID", item.productID);
                    sqlcmd3.Parameters.AddWithValue("@price", item.price);
                    sqlcmd3.Parameters.AddWithValue("@quantity", "1");
                    sqlcmd3.Parameters.AddWithValue("@discount", "1");
                    sqlcmd3.ExecuteNonQuery();
                }
                sqlcon.Close();
            }
            catch (Exception)
            {
            }
        }


        public static List<siparisler> siparislerigetir()
        {
            List<siparisler> lst = new List<siparisler>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select SUM(price) as tutar,od.OrderID,OrderDate,ShipAddress,invoiceAddress,RequiredDate,namesurname,telefon,status,Odemeyontemi from Orders as o  inner join uyeler as u on o.userID = u.userID inner join orderdetails as od on od.OrderID = o.orderID group by od.OrderID ,OrderDate,ShipAddress,invoiceAddress,RequiredDate,namesurname,telefon,status,Odemeyontemi", sqlcon);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                siparisler s = new siparisler();
                s.siparisID = Convert.ToInt32(sdr["orderID"]);
                s.orderdate = sdr["OrderDate"].ToString();
                s.siparisveren = sdr["namesurname"].ToString();
                s.shipaddres = sdr["ShipAddress"].ToString();
                s.telefon = sdr["telefon"].ToString();
                s.invoiceaddres = sdr["invoiceAddress"].ToString();
                s.price = Convert.ToDecimal(sdr["tutar"]);
                s.requireddate = sdr["RequiredDate"].ToString();
                s.odemeyontemi = sdr["OdemeYontemi"].ToString();
                s.status = Convert.ToInt32(sdr["status"]);
                lst.Add(s);
            }
            sqlcon.Close();
            return lst;
        }


        public static List<siparisler> siparislerigetirID(int ID)
        {
            List<siparisler> lst = new List<siparisler>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select SUM(price) as tutar,od.OrderID,OrderDate,ShipAddress,invoiceAddress,RequiredDate,namesurname,telefon,status,Odemeyontemi,ShipVia,Freight,email,faturaadresi,teslimatadresi, u.userID from orders as o inner join uyeler as u on o.userID = u.userID inner join orderdetails as od on od.OrderID = o.orderID where od.OrderID=@oid group by od.OrderID ,OrderDate,ShipAddress,invoiceAddress,RequiredDate,namesurname,telefon,status,Odemeyontemi,ShipVia,Freight,email,faturaadresi,teslimatadresi,u.userID", sqlcon);
            sqlcmd.Parameters.AddWithValue("@oid", ID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                siparisler s = new siparisler();
                s.siparisID = Convert.ToInt32(sdr["orderID"]);
                s.orderdate = sdr["OrderDate"].ToString();
                s.siparisveren = sdr["namesurname"].ToString();
                s.shipaddres = sdr["ShipAddress"].ToString();
                s.telefon = sdr["telefon"].ToString();
                s.invoiceaddres = sdr["invoiceAddress"].ToString();
                s.price = Convert.ToDecimal(sdr["tutar"]);
                s.requireddate = sdr["RequiredDate"].ToString();
                s.odemeyontemi = sdr["OdemeYontemi"].ToString();
                s.status = Convert.ToInt32(sdr["status"]);
                s.shipvia = Convert.ToInt32(sdr["ShipVia"]);
                s.frieght = Convert.ToDecimal(sdr["Freight"]);
                s.email = sdr["email"].ToString();
                s.faturaadresi = sdr["faturaadresi"].ToString();
                s.teslimatadresi = sdr["teslimatadresi"].ToString();
                s.userID = sdr["userID"].ToString();
                lst.Add(s);
            }
            sqlcon.Close();
            return lst;
        }


        public static List<siparisler> detaylarigetir(int ID)
        {
            List<siparisler> lst = new List<siparisler>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from orderdetails as od inner join products as p on od.productID=p.productID where orderID=@ID", sqlcon);
            sqlcmd.Parameters.AddWithValue("@ID", ID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                siparisler s = new siparisler();
                s.siparisID = Convert.ToInt32(sdr["orderID"]);
                s.productID = Convert.ToInt32(sdr["productID"]);
                s.productname = sdr["productname"].ToString();
                s.sepetmiktar = Convert.ToInt32(sdr["quantity"]);
                s.price = Convert.ToInt32(sdr["price"]);
                s.discount = Convert.ToDecimal(sdr["discount"]);
                lst.Add(s);
            }
            sqlcon.Close();
            return lst;
        }



        public List<siparisler> xmlsepetoku()
        {
            //biz xml den okuduk verileri ,dolayısıyla asagıdaki XElement satırını yazdık,eger veritabanına baglanıp (visual studiodan , linq yaratıp diagram olustursaydık) , asagıdaki,  var sepetler den itibaren aynı seyleri yazacaktık.Xelement satırı yerine , List<products> plist = db.products.toList gibi birseyler yazacaktık.

            string urunrrr = "";
            XElement xe = XElement.Load("C:\\Users\\sedat\\Documents\\Visual Studio 2013\\Projects\\elctronixMVC\\elctronixMVC\\App_Data\\sepet.xml");

            var sepetler = from c in xe.Elements("sepet")
                           where string.Equals(c.Element("userID").Value, "10") && string.Equals(c.Element("durum").Value, "0")
                           select
                           new
                           {
                               //sepetler sorgusu uyanların , xml deki  urunlerr icerigini buradan okuruz 
                               ukat = c.Element("urunlerr").Value
                           };

            List<siparisler> sip = new List<siparisler>();
            sepettutar = 0;
            kalem = 0;
            foreach (var items in sepetler)
            {
                urunrrr = items.ukat.ToString();
               

                var collection = (from p in db.products
                                  where p.productID == Convert.ToInt32(urunrrr)
                                  select p).ToList();

                foreach (var item in collection)
                {
                    siparisler s = new siparisler();
                    s.brand = item.brand.ToString();
                    s.model = item.model.ToString();
                    s.productname = item.productname.ToString();
                    s.productID = Convert.ToInt32(item.productID);
                    s.price = Convert.ToDecimal(item.price);
                    sepettutar += s.price;
                    kalem++;
                    s.categoryID = Convert.ToInt32(item.categoryID);
                    sip.Add(s);
                }
            }
            return sip;
        }







        public void xmlsepetyaz()
        {
           var doc = XDocument.Load("C:\\Users\\sedat\\Documents\\Visual Studio 2013\\Projects\\elctronixMVC\\elctronixMVC\\App_Data\\sepet.xml");


            var newElement =  new XElement("sepet", 
                              new XElement("userID",userID),
                              new XElement("durum","0"),
                              new XElement("urunlerr",productID));
                      
            doc.Element("root").Add(newElement);

            doc.Save("C:\\Users\\sedat\\Documents\\Visual Studio 2013\\Projects\\elctronixMVC\\elctronixMVC\\App_Data\\sepet.xml");
        }











    }
}